"""
生成入库清单
doc https://openpyxl.readthedocs.io/en/stable/
ref http://www.52codes.net/develop/shell/58896.html
"""

from openpyxl import Workbook, load_workbook
from openpyxl.utils import get_column_letter, column_index_from_string
from openpyxl.styles import NamedStyle, Border, Side, Alignment, Font, PatternFill
import xlwt
import math, os


# 定义格式
emphasised = NamedStyle(name="emphasised")
emphasised.font = Font(bold=True, size=12)
bd = Side(style='thin', color="000000")
emphasised.border = Border(left=bd, top=bd, right=bd, bottom=bd)
emphasised.alignment = Alignment(horizontal='center', vertical='center')

bordered = NamedStyle(name="bordered")
bordered.border = Border(left=bd, top=bd, right=bd, bottom=bd)
bordered.alignment = Alignment(horizontal='center', vertical='center')

filled = NamedStyle(name="filled")
filled.font = Font(bold=True, size=12)
filled.border = Border(left=bd, top=bd, right=bd, bottom=bd)
filled.alignment=Alignment(horizontal='center', vertical='center')
filled.fill = PatternFill("solid", fgColor="dbe5f0")

# 读入的数据行
dataList = []
# 采购单号
caigou_danhao = []

def excelWork(xls, outDir):
  global dataList, caigou_danhao, emphasised, bordered, filled

  # 打开文件
  wb = load_workbook(xls)
  safe_add_named_style(wb, emphasised)
  safe_add_named_style(wb, bordered)
  safe_add_named_style(wb, filled)

  jhb = wb['入仓计划表']
  if jhb is None :
    exit(False)

  # 读入数据表格
  columnNames = ("productNo", "sku", "title", "gzNow", "gzNew", "bjNow", "bjNew", "shNow", "shNew", "whNow",
    "whNew", "totalNow", "totalNew", "qyHas", "perBox")
  # 数据从行 3 开始,列A-O
  # 排除最后一行汇总
  for row in jhb.iter_rows(min_row=3, max_col=column_index_from_string('O'), max_row=jhb.max_row-2):
    item = dict(zip(columnNames, [cell.value for cell in row]))
    dataList.append(item)

  # 采购单号
  for i in range(2, 6):
    rec = [cell.value for cell in jhb['Q%d'%i:'R%d'%i][0]]
    caigou_danhao.append(dict(zip(('storage', 'orderNo'), rec)))

  ################
  ### 生成 入库清单
  sheet = safe_create_sheet(wb, "入库清单")
  # 表头
  # sheet.append(['京东仓入仓清单'])
  # sheet.merge_cells('A1:G1')
  sheet.append(['sku/商品标识', '商品名称', ' 广州仓', '北京仓', '上海仓', '武汉仓', '合计'])
  # 数据行
  for row in dataList:
    total = sum((row['gzNew'] or 0, row['bjNew'] or 0, row['shNew'] or 0, row['whNew'] or 0))
    if total <= 0:
      continue
    sheet.append((row['sku'], row['title'], 
      row['gzNew'], row['bjNew'], row['shNew'], row['whNew'], 
      total
    ))

  # 修饰样式
  sheet.column_dimensions['A'].width = 20
  sheet.column_dimensions['B'].width = 25
  for row in sheet['A1:G2']:
    for cell in row:
      cell.style = 'emphasised'
  for row in sheet.iter_rows(min_row=3, max_col=sheet.max_column, max_row=sheet.max_row):
    for cell in row:
      cell.style = 'bordered'

  ################
  ### 生成 TC送货交接单)
  sheet = safe_create_sheet(wb, "TC交接单")
  # 表头
  sheet.append(['TC送货交接单'])
  sheet.merge_cells('A1:E1')
  sheet.append(['TC预约号',	'',	'21030911204',	'TC预约送货日期',	'2021-03-09 14:00-16:00'])
  sheet.merge_cells('A2:B2')
  sheet.append(['供应商名称',	'',	'广州华擎网络科技有限公司',	'总箱数', ''])
  sheet.merge_cells('A3:B3')
  sheet.append(['采购单号',	'箱数',	'商品名称',	'目的库房',	'商品总数量/个'])

  # 数据行
  # 广州仓
  rows = [row for row in dataList if row['gzNew']]
  if rows:
    append_tc(sheet, rows, 'gzNew', '广州公共平台仓8号库')
  # 北京仓
  rows = [row for row in dataList if row['bjNew']]
  if rows:
    append_tc(sheet, rows, 'bjNew', '北京公共平台仓7号库')
  # 上海仓
  rows = [row for row in dataList if row['shNew']]
  if rows:
    append_tc(sheet, rows, 'shNew', '上海公共平台仓10号库')
  # 武汉仓
  rows = [row for row in dataList if row['whNew']]
  if rows:
    append_tc(sheet, rows, 'whNew', '武汉百货B家居日用仓2号库')
  # 合计
  sheet.append(['', boxesTotal, '', '', numTotal])

  # 修饰样式
  sheet.column_dimensions['A'].width = 20
  sheet.column_dimensions['C'].width = 25
  sheet.column_dimensions['D'].width = 25
  sheet.column_dimensions['E'].width = 15
  for row in sheet.iter_rows(min_row=5, max_col=sheet.max_column, max_row=sheet.max_row-1):
    for cell in row:
      cell.style = 'bordered'
  sheet['A1'].style = 'emphasised'
  for k in ('A2', 'B2', 'D2', 'A3', 'B3', 'D3'):
    sheet[k].style = 'filled'
  for cell in sheet[4]:
    cell.style = 'filled'
  for k in ('C1', 'E1', 'C2', 'E2'):
    sheet[k].style = 'bordered'

  ###################
  ### 生成 箱标打印数据
  sheet = safe_create_sheet(wb, "箱标打印数据")
  # 表头
  sheet.append(['供应商名称', '采购单号',	'目标库房',	'sku',	'商品名称',	'箱数',	'总箱数'])
  
  # 广州仓
  rows = [row for row in dataList if row['gzNew']]
  if rows:
    append_xb(sheet, rows, 'gzNew', '广州公共平台仓8号库')
  # 北京仓
  rows = [row for row in dataList if row['bjNew']]
  if rows:
    append_xb(sheet, rows, 'bjNew', '北京公共平台仓7号库')
  # 上海仓
  rows = [row for row in dataList if row['shNew']]
  if rows:
    append_xb(sheet, rows, 'shNew', '上海公共平台仓10号库')
  # 武汉仓
  rows = [row for row in dataList if row['whNew']]
  if rows:
    append_xb(sheet, rows, 'whNew', '武汉百货B家居日用仓2号库')

  # 修饰样式
  sheet.column_dimensions['A'].width = 30
  sheet.column_dimensions['B'].width = 20
  sheet.column_dimensions['C'].width = 25
  sheet.column_dimensions['D'].width = 20
  sheet.column_dimensions['E'].width = 30
  sheet.column_dimensions['F'].width = 8
  sheet.column_dimensions['G'].width = 8
  for row in sheet.iter_rows(min_row=1, max_col=sheet.max_column, max_row=sheet.max_row):
    for cell in row:
      cell.style = 'bordered'
  for cell in sheet[1]:
    cell.style = 'emphasised'

  # 写入文件 入库清单
  wb.save(os.path.join(outDir, '京东仓入仓计划表.xlsx'))

  # 广州仓
  rows = [row for row in dataList if row['gzNew']]
  gzNew = len(rows)
  if rows:
    generate_orders_gz2(rows, 'gzNew', '采购单导入-广州仓', outDir)
  # 北京仓
  rows = [row for row in dataList if row['bjNew']]
  bjNew = len(rows)
  if rows:
    generate_orders_gz2(rows, 'bjNew', '采购单导入-北京仓', outDir)
  # 上海仓
  rows = [row for row in dataList if row['shNew']]
  shNew = len(rows)
  if rows:
    generate_orders_gz2(rows, 'shNew', '采购单导入-上海仓', outDir)
  # 武汉仓
  rows = [row for row in dataList if row['whNew']]
  whNew = len(rows)
  if rows:
    generate_orders_gz2(rows, 'whNew', '采购单导入-武汉仓', outDir)

  return True, gzNew, bjNew, shNew, whNew

# TC送货交接单
tc_start_row_num = 5
boxesTotal = 0
numTotal = 0
def append_tc(sheet, rows, num_key, storage):
  global tc_start_row_num, caigou_danhao, boxesTotal, numTotal

  current_row_num = tc_start_row_num
  for row in rows:
    # 采购单号
    orderNo = ''
    for item in caigou_danhao:
      if item['storage'] == storage:
        orderNo = item['orderNo']
        break
    packages = math.ceil(row[num_key] / row['perBox'])
    sheet.append((orderNo, packages, row['title'], storage, row[num_key]))
    boxesTotal += packages
    numTotal += row[num_key]
    
    current_row_num += 1
  # 合并单元格
  sheet.merge_cells('D{}:D{}'.format(tc_start_row_num, current_row_num - 1))

  tc_start_row_num = current_row_num

# 箱标
company = '广州华擎网络科技有限公司'
xb_start_row_num = 2
def append_xb(sheet, rows, num_key, storage):
  global xb_start_row_num, caigou_danhao, company

  current_row_num = xb_start_row_num
  boxInStorage = 0
  for row in rows:
    # 采购单号
    orderNo = ''
    for item in caigou_danhao:
      if item['storage'] == storage:
        orderNo = item['orderNo']
        break
    boxTotal = math.ceil(row[num_key] / row['perBox'])
    boxNos = ['第%d箱'%i for i in range(1, boxTotal+1)]
    for boxNo in boxNos:
      sheet.append((company, orderNo, storage, row['sku'].replace('POP', ''), row['title'], 
        boxNo, '共%d箱'%boxTotal, row[num_key]))
      current_row_num += 1
    
    boxInStorage += boxTotal

  # 覆盖共*箱, 按照仓库维度统计
  for i in range(boxInStorage):
    sheet['F%d'%(i+xb_start_row_num)] = '第%d箱'%(i+1)
    sheet['G%d'%(i+xb_start_row_num)] = '共%d箱'%boxInStorage

  xb_start_row_num = current_row_num


########################
### 采购单
def generate_orders_gz(rows, num_key, title, outDir):
  global bordered
  wb = Workbook()
  safe_add_named_style(wb, bordered)
  sheet = wb.active
  sheet.title = title
  # 表头
  sheet.append(['*ECLP事业部商品编码',	'商家商品编码',	'商品数量（个）',	'代贴条码(是/否)',	'单价',	'质检比例（大件且开通质检服务）0-100',	'是否序列号入库（是/否）'])
  for row in rows:
    sheet.append((row['productNo'], row['sku'].replace('POP', ''), row[num_key], '否', '', row[num_key]))

  # 修饰样式
  sheet.column_dimensions['A'].width = 30
  sheet.column_dimensions['B'].width = 20
  sheet.column_dimensions['C'].width = 15
  sheet.column_dimensions['D'].width = 15
  sheet.column_dimensions['E'].width = 10
  sheet.column_dimensions['F'].width = 40
  sheet.column_dimensions['G'].width = 30
  for row in sheet.iter_rows(min_row=1, max_row=sheet.max_row, max_col=sheet.max_column):
    for cell in row:
      cell.style = 'bordered'

  wb.save(os.path.join(outDir, '%s.xlsx'%title))

# 生成 xls 的版本
def generate_orders_gz2(rows, num_key, title, outDir):
  # bordered style
  style0 = xlwt.easyxf('align: wrap on, vert centre, horiz center; border: left 1, right 1, top 1, bottom 1')
  # worksheet
  wb = xlwt.Workbook()
  ws = wb.add_sheet(title)
  
  # 列名
  c = 0
  for item in ['*ECLP事业部商品编码',	'商家商品编码',	'商品数量（个）',	'代贴条码(是/否)',	'单价',	'质检比例（大件且开通质检服务）0-100',	'是否序列号入库（是/否）']:
    ws.write(0, c, item, style0)
    c += 1
  # 行数据
  j = 1
  for row in rows:
    c = 0
    for item in (row['productNo'], row['sku'].replace('POP', ''), row[num_key], '否', '', row[num_key], ''):
      ws.write(j, c, item, style0)
      c += 1
    j += 1

  wb.save(os.path.join(outDir, '%s.xls'%title))

# sheetName 存在先删除
def safe_create_sheet(wb, sheetName):
  try:
    wb.remove(wb[sheetName])
  except:
    pass
  return wb.create_sheet(sheetName)

def safe_add_named_style(wb, namedStyle):
  try:
    wb.add_named_style(namedStyle)
  except:
    pass